using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using CE.Database;
using CE.Security;
/// <summary>
/// Summary description for AccountDB
/// </summary>
/// 
namespace CE.User
{
    public class AccountDB
    {
        public AccountDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Int64 Insert(Account account)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocUserInsertUpdateSingleItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CEID", account.CEID);
            _sPara.Add("@UserId", account.UserId);
            _sPara.Add("@Email", account.Email.ToLower());
            _sPara.Add("@AuthCode", account.AuthCode);
            _sPara.Add("@AuthSalt", account.AuthSalt);
            _sPara.Add("@AuthHint", account.AuthHint);
            _sPara.Add("@UsrTypeId", account.UserTypeId);
            _sPara.Add("@IsAcceptTC", account.IsAcceptTC);
            _sPara.Add("@RefCEUserId", account.RefCEUserID);
            _sPara.Add("@Status", account.Status);
            _sPara.Add("@Info1", account.Info1);
            _sPara.Add("@Info2", account.Info2);
            _sPara.Add("@Info3", account.Info3);
            _sPara.Add("@Info4", account.Info4);
            _sPara.Add("@Info5", account.Info5);
            _sPara.Add("@Info6", account.Info6);
            _sPara.Add("@Info7", account.Info7);
            _sPara.Add("@Info8", account.Info8);
            _sPara.Add("@Info9", account.Info9);
            _sPara.Add("@Info10", account.Info10);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public bool UnRejectUser(Int64 ceuserid)
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "UPDATE [tblCEUser] SET [IsReject] = NULL WHERE CEID = @CEUserId ";
            
            SortedList _spara = new SortedList();
            _spara.Add("@CEUserId", ceuserid);

            int result = _db.ExecuteNonSqlStatement(strSQL, _spara);
            return result > 0;
        }

        public Account CheckEmail(string email)
        {
            Account _account = null;
            MyDatabase _db = new MyDatabase();
            string strSQL = "Select CEID,UserId,Email,AuthCode,AuthSalt,UsrTypeId,RefCEUserId,Status,Info1,Info2 from tblCEUser Where [Email] = @Email AND [Status] = 1";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", email);

            using (DataTable _dt = _db.GetDataTable(strSQL, _spara))
            {
                if (_dt!=null && _dt.Rows.Count > 0)
                {
                    _account = new Account();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        public Account CheckEmail(string Email, int UserTypeId)
        {
            Account _account = null;
            MyDatabase _db = new MyDatabase();
            //string strSQL = "Select CEID, UserId, Email, AuthHint, AuthCode, AuthSalt, UsrTypeId, Status, RefCEUserId from tblCEUser Where [Email] = @Email And [UsrTypeId]=@UserTypeId";
            string _spName = "[sprocUserSelectSingleItemByEmail]";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", Email);
            _spara.Add("@Status", true);
            _spara.Add("@UsrTypeId", UserTypeId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new Account();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        public Account GetUserAccount(string Email, int UserTypeId)
        {
            Account _account = null;
            MyDatabase _db = new MyDatabase();
            //string strSQL = "Select CEID, Email from tblCEUser Where [Email] = @Email And [UsrTypeId]=@UserTypeId";
            string _spName = "[sprocUserSelectSingleItemIsEmailExist]";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", Email);
            _spara.Add("@UsrTypeId", UserTypeId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new Account();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        //Meng Pan on 11 Sep 2014 -- Start
        public Account GetUserAccount(string Email, string Mobile, int UserTypeId)
        {
            Account _account = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserSelectSingleItemIsEmailMobileExist]";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", Email);
            _spara.Add("@Mobile", Mobile);
            _spara.Add("@UsrTypeId", UserTypeId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new Account();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }
        //Meng Pan on 11 Sep 2014 -- End

        //Sakthi on 27 Jun 2011 -- Start
        public Account GetUserAccount(Int64 CEID, int UserTypeId)
        {
            Account _account = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserSelectSingleItemByUserId]";

            SortedList _spara = new SortedList();
            _spara.Add("@CEID", CEID);
            _spara.Add("@UsrTypeId", UserTypeId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new Account();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }
        //Sakthi on 27 Jun 2011 -- End

        public Account GetUserAccountByAuthKey(string AuthSalt, string GUID)
        {
            Account _account = null;
            MyDatabase _db = new MyDatabase();
            //string strSQL = "Select CEID, Email from tblCEUser Where [Email] = @Email And [UsrTypeId]=@UserTypeId";
            string _spName = "[sprocUserSelectSingleItemByAuthKey]";

            SortedList _spara = new SortedList();
            _spara.Add("@AuthKey", AuthSalt);
            _spara.Add("@GUID", GUID);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new Account();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        public Account GetUserLoginFromMailByGUID(string GUID, Int64 CarId, int TrackType)
        {
            Account _account = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserLoginFromMailByGUID]";

            SortedList _spara = new SortedList();
            _spara.Add("@GUID", GUID);
            _spara.Add("@CarId", CarId);
            _spara.Add("@TrackType", TrackType);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new Account();
                    _account = FillDataRecordAutoLoinFromMail(_dt);
                }
            }
            return _account;
        }

        public bool IsAlreadyExist(string Email, int UserTypeId)
        {
            bool _result = false;
            MyDatabase _db = new MyDatabase();
            //string strSQL = "Select CEID, Email from tblCEUser Where [Email] = @Email And [UsrTypeId]=@UserTypeId";
            string _spName = "[sprocUserSelectSingleItemIsEmailExist]";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", Email);
            _spara.Add("@UsrTypeId", UserTypeId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _result = true;
                }
            }
            return _result;
        }

        //Meng Pan on 11 Sep 2014 -- Start
        public bool IsAlreadyExist(string Email, string Mobile, int UserTypeId)
        {
            bool _result = false;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserSelectSingleItemIsEmailMobileExist]";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", Email);
            _spara.Add("@Mobile", Mobile);
            _spara.Add("@UsrTypeId", UserTypeId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _result = true;
                }
            }
            return _result;
        }
        //Meng Pan on 11 Sep 2014 -- End

        public Account GetAccount(Int64 CEID)
        {
            Account _account = null;
            MyDatabase _db = new MyDatabase();
            //string strSQL = "Select CEID, UserId, Email, AuthHint, AuthCode, AuthSalt, UsrTypeId, Status, RefCEUserId from tblCEUser Where [CEID] = @CEID";
            string _spName = "[sprocUserSelectSingleItem]";

            SortedList _spara = new SortedList();
            _spara.Add("@CEID", CEID);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new Account();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        public string GetGUID(Int64 CEID)
        {
            MyDatabase _db = new MyDatabase();
            //string strSQL = "Select ROWGUID from tblCEUser Where [CEID] = @CEID";
            string _spName = "[sprocUserSelectSingleItemGUID]";

            SortedList _spara = new SortedList();
            _spara.Add("@CEID", CEID);

            object _result = _db.ExecuteScalarSP(_spName, _spara);

            return _result.ToString();
        }

        public bool ActivateAccount(string GUID)
        {
            MyDatabase _db = new MyDatabase();
            //string strSQL = "Update tblCEUser Set [Status] = 1 Where [rowguid] = @GUID";
            string _spName = "[sprocUserActivateAccount]";

            SortedList _spara = new SortedList();
            _spara.Add("@GUID", GUID);

            int _result = _db.ExecuteNonSqlSP(_spName, _spara);

            return _result > 0;
        }

        public bool ResetPassword(string NewPassword, string AuthKey, string GUID)
        {
            MyDatabase _db = new MyDatabase();
            //string strSQL = "Update tblCEUser Set [AuthCode] = @NewPassword Where [rowguid] = @GUID AND [AuthSalt]= @AuthKey";
            string _spName = "[sprocUserResetPassword]";

            SortedList _spara = new SortedList();
            _spara.Add("@NewPassword", NewPassword);
            _spara.Add("@GUID", GUID);
            _spara.Add("@AuthKey", AuthKey);

            int _result = _db.ExecuteNonSqlSP(_spName, _spara);

            return _result > 0;
        }

        private Account FillDataRecord(DataTable myDataTable)
        {
            Account myAccount = new Account();
            myAccount.CEID = Convert.ToInt64(myDataTable.Rows[0]["CEID"].ToString());
            myAccount.UserId = myDataTable.Rows[0]["UserId"].ToString();
            myAccount.Email = myDataTable.Rows[0]["Email"].ToString();
            myAccount.AuthCode = myDataTable.Rows[0]["AuthCode"].ToString();
            myAccount.AuthSalt = myDataTable.Rows[0]["AuthSalt"].ToString();
            myAccount.UserTypeId = Convert.ToInt32(myDataTable.Rows[0]["UsrTypeId"].ToString());
            myAccount.RefCEUserID = Convert.ToInt64(myDataTable.Rows[0]["RefCEUserId"].ToString());
            myAccount.Status = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
            if (myDataTable.Rows[0]["Info1"] != null)
            {
                myAccount.Info1 = myDataTable.Rows[0]["Info1"].ToString();
            }
            if (myDataTable.Rows[0]["Info2"] != null)
            {
                myAccount.Info2 = myDataTable.Rows[0]["Info2"].ToString();
            }

            return myAccount;
        }

        private Account FillDataRecordAutoLoinFromMail(DataTable myDataTable)
        {
            Account myAccount = new Account();
            myAccount.CEID = Convert.ToInt64(myDataTable.Rows[0]["CEID"].ToString());
            myAccount.UserId = myDataTable.Rows[0]["UserId"].ToString();
            myAccount.Email = myDataTable.Rows[0]["Email"].ToString();
            myAccount.AuthCode = myDataTable.Rows[0]["AuthCode"].ToString();
            myAccount.AuthSalt = myDataTable.Rows[0]["AuthSalt"].ToString();
            myAccount.UserTypeId = Convert.ToInt32(myDataTable.Rows[0]["UsrTypeId"].ToString());
            myAccount.RefCEUserID = Convert.ToInt64(myDataTable.Rows[0]["RefCEUserId"].ToString());
            myAccount.Status = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
            myAccount.UserResultId = Convert.ToInt64(myDataTable.Rows[0]["UserResultId"].ToString());
            if (myDataTable.Rows[0]["Info1"] != null)
            {
                myAccount.Info1 = myDataTable.Rows[0]["Info1"].ToString();
            }
            if (myDataTable.Rows[0]["Info2"] != null)
            {
                myAccount.Info2 = myDataTable.Rows[0]["Info2"].ToString();
            }

            return myAccount;
        }

        private Account FillDataRecord(IDataRecord myDataRecord)
        {
            Account myAccount = new Account();
            myAccount.CEID = myDataRecord.GetInt64(myDataRecord.GetOrdinal("CEID"));
            myAccount.UserId = myDataRecord.GetString(myDataRecord.GetOrdinal("UserId"));
            myAccount.Email = myDataRecord.GetString(myDataRecord.GetOrdinal("Email"));
            myAccount.AuthCode = myDataRecord.GetString(myDataRecord.GetOrdinal("AuthCode"));
            myAccount.AuthSalt = myDataRecord.GetString(myDataRecord.GetOrdinal("AuthSalt"));
            myAccount.RefCEUserID = myDataRecord.GetInt64(myDataRecord.GetOrdinal("RefUserId"));
            myAccount.Status = myDataRecord.GetBoolean(myDataRecord.GetOrdinal("Status"));
            return myAccount;
        }

        //public DataSet GetUserStatus(Int64 CEUserId)
        //{
        //    DataSet _result = null;
        //    MyDatabase _db = new MyDatabase();
        //    string _spName = "[sprocUserStatusSelectSingleItem]";

        //    SortedList _spara = new SortedList();
        //    _spara.Add("@RefCEUserId", CEUserId);


        //    using (DataSet _ds = _db.GetDataSet(_spName, _spara))
        //    {
        //        if (_ds != null && _ds.Tables[0].Rows.Count > 0)
        //        {
        //            _result = _ds;
        //        }
        //    }
        //    return _result;
        //}

    }
}